-- @owner: opentestcase013
-- @date: 2023/7/31
-- @testpoint: 普通非压缩表，修改compresstype=2, compress_level不同取值，部分步骤合理报错
-- modified by opentestcase013 at 2023/9/26:增加checkpoint步骤

--step1:创建函数查询表文件相对路径;expect:成功
drop function if exists compress_func_findpath(character varying);
create or replace function compress_func_findpath(character varying)
returns character varying
language plpgsql
as
$body$
declare
relpath character varying;
begin
relpath = (select pg_relation_filepath(relname::regclass) from pg_class where relname = $1);
return relpath;
end;
$body$;
/

--step2:建普通表并插入数据;expect:成功
drop table if exists t_alter_rowcompress_0011 cascade;
create table t_alter_rowcompress_0011(columnone integer ,columntwo char(50) ,columnthree varchar(50) ,columnfour integer ,columnfive char(50) ,columnsix varchar(50) ,columnseven char(50) ,columneight char(50) ,columnnine varchar(50) ,columnten varchar(50) ,columneleven char(50) ,columntwelve char(50) ,columnthirteen varchar(50) ,columnfourteen char(50) ,columnfifteem varchar(50));
insert into t_alter_rowcompress_0011 values(generate_series(0, 1600),'test','abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwx', 2, 'abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwx', 'abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwx', 'abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwx', 'abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwx', 'abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwx', 'abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwx', 'abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwx', 'abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwx', 'abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwx', 'abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwx', 'abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwx');

--step3:修改压缩参数compresstype=2,compress_level=-31,并查看压缩率;expect:成功
alter table t_alter_rowcompress_0011 set (compresstype=2,compress_level=-31);
checkpoint;
select is_compress, file_count, logic_size, physic_size, compress_ratio from pg_catalog.compress_ratio_info(compress_func_findpath('t_alter_rowcompress_0011'));

--step4:修改压缩参数compresstype=2,compress_level=1,并查看压缩率;expect:成功
alter table t_alter_rowcompress_0011 set (compresstype=2,compress_level=1);
checkpoint;
select is_compress, file_count, logic_size, physic_size, compress_ratio from pg_catalog.compress_ratio_info(compress_func_findpath('t_alter_rowcompress_0011'));

--step5:修改压缩参数compresstype=2,compress_level=31,并查看压缩率;expect:成功
alter table t_alter_rowcompress_0011 set (compresstype=2,compress_level=31);
checkpoint;
select is_compress, file_count, logic_size, physic_size, compress_ratio from pg_catalog.compress_ratio_info(compress_func_findpath('t_alter_rowcompress_0011'));

--step6:修改压缩参数compresstype=2,compress_level为取值范围外值;expect:合理报错
alter table t_alter_rowcompress_0011 set (compresstype=2,compress_level=99);
alter table t_alter_rowcompress_0011 set (compresstype=2,compress_level=-99);

--step7:修改压缩参数compresstype=2,compress_level为无效值;expect:合理报错
alter table t_alter_rowcompress_0011 set (compresstype=2,compress_level='test');
alter table t_alter_rowcompress_0011 set (compresstype=2,compress_level=2.2);

--step8:清理环境;expect:成功
drop table if exists t_alter_rowcompress_0011 cascade;
drop function if exists compress_func_findpath(character varying);